/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.proximati.biprime.server.olapql.language.query.translator;

import br.com.proximati.biprime.server.olapql.language.query.AbstractQueryVisitor;
import br.com.proximati.biprime.server.olapql.language.measure.translator.MeasureSqlTranslator;
import br.com.proximati.biprime.server.olapql.language.query.SimpleNode;
import br.com.proximati.biprime.metadata.entity.CubeLevel;
import br.com.proximati.biprime.metadata.entity.Filter;
import br.com.proximati.biprime.metadata.entity.Level;
import br.com.proximati.biprime.metadata.entity.Metadata;
import br.com.proximati.biprime.metadata.entity.Property;
import br.com.proximati.biprime.server.olapql.language.query.ASTAdditiveExpression;
import br.com.proximati.biprime.server.olapql.language.query.ASTAndCondition;
import br.com.proximati.biprime.server.olapql.language.query.ASTAxis;
import br.com.proximati.biprime.server.olapql.language.query.ASTCompare;
import br.com.proximati.biprime.server.olapql.language.query.ASTCube;
import br.com.proximati.biprime.server.olapql.language.query.ASTDateLiteral;
import br.com.proximati.biprime.server.olapql.language.query.ASTEndsWithExpression;
import br.com.proximati.biprime.server.olapql.language.query.ASTFilter;
import br.com.proximati.biprime.server.olapql.language.query.ASTFilterExpression;
import br.com.proximati.biprime.server.olapql.language.query.ASTInExpression;
import br.com.proximati.biprime.server.olapql.language.query.ASTLevel;
import br.com.proximati.biprime.server.olapql.language.query.ASTLevelOrMeasureOrFilter;
import br.com.proximati.biprime.server.olapql.language.query.ASTLikeExpression;
import br.com.proximati.biprime.server.olapql.language.query.ASTMultiplicativeExpression;
import br.com.proximati.biprime.server.olapql.language.query.ASTNegation;
import br.com.proximati.biprime.server.olapql.language.query.ASTNumberLiteral;
import br.com.proximati.biprime.server.olapql.language.query.ASTOrCondition;
import br.com.proximati.biprime.server.olapql.language.query.ASTProperty;
import br.com.proximati.biprime.server.olapql.language.query.ASTPropertyNode;
import br.com.proximati.biprime.server.olapql.language.query.ASTSelect;
import br.com.proximati.biprime.server.olapql.language.query.ASTStartsWithExpression;
import br.com.proximati.biprime.server.olapql.language.query.ASTStringLiteral;
import br.com.proximati.biprime.server.olapql.language.query.QueryParser;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashSet;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;
import java.util.Stack;
import org.apache.commons.io.IOUtils;

/**
 *
 * @author luiz
 */
public class QuerySqlTranslator extends AbstractQueryVisitor {

    private Stack<Integer> axisNodeVisitationStack = new Stack<Integer>();

    @Override
    public void visit(ASTSelect node, Object data) throws Exception {
        getOutput(data).append("select ");

        super.visit(node, data);

        if (getQueryMetadata(data).getMetadataReferencedByAxisNodes().size() > 0) {
            StringBuilder groupBy = new StringBuilder();

            for (SimpleNode axisNode : getQueryMetadata(data).getAxisNodeList()) {
                Metadata metadata = getQueryMetadata(data).getMetadataReferencedBy(axisNode);
                if (metadata instanceof Level || metadata instanceof Property || metadata instanceof Filter)
                    groupBy.append(((TranslationContext) data).getAxisNodePositionsMap().get(axisNode)).append(", ");
            }

            if (groupBy.length() > 0) {
                groupBy.delete(groupBy.length() - 2, groupBy.length());
                getOutput(data).append(" group by ").append(groupBy);
            }
        }

        getContext(data).setTranslatedSelect(node);
    }

    @Override
    public void visit(ASTCube node, Object data) throws Exception {
        // delete last comma generated by axis translation
        getOutput(data).delete(getOutput(data).length() - 2, getOutput(data).length());

        getOutput(data).append(" from ");

        Set<String> tables = new HashSet<String>();

        tables.add(TranslationUtils.tableExpression(getQueryMetadata(data).getCube().getSchemaName(),
                getQueryMetadata(data).getCube().getTableName()));

        for (Level level : levelsPresent((TranslationContext) data))
            for (Level lowerLevel : level.getLowerLevels())
                tables.add(TranslationUtils.tableExpression(lowerLevel.getSchemaName(), lowerLevel.getTableName()));

        for (String table : tables)
            getOutput(data).append(table).append(", ");

        if (!tables.isEmpty())
            getOutput(data).delete(getOutput(data).length() - 2, getOutput(data).length());

        getOutput(data).append(" where ");

        whereExpression((TranslationContext) data);
    }

    @Override
    public void visit(ASTNegation node, Object data) throws Exception {
        if (getOutput(data).charAt(getOutput(data).length() - 1) != ' ')
            getOutput(data).append(" not");
        else
            getOutput(data).append("not ");

        visit(node.jjtGetChild(0), data);
    }

    @Override
    public void visit(ASTLevel node, Object data) throws Exception {
        Level level = getQueryMetadata(data).getAllReferencedMetadata().getLevel(node.jjtGetValue().toString());
        getOutput(data).append(TranslationUtils.columnExpression(level.getTableName(), level.getCodeProperty().getColumnName()));
    }

    @Override
    public void visit(ASTFilter node, Object data) throws Exception {
        Filter filter = getQueryMetadata(data).getAllReferencedMetadata().getFilter(node.jjtGetValue().toString());
        translate(filter.getExpression(), (TranslationContext) data);
    }

    @Override
    public void visit(ASTPropertyNode node, Object data) throws Exception {
        axisNodeVisitationStack.push(childIndex(node));

        Property property = getQueryMetadata(data).getAllReferencedMetadata().getProperty(node.jjtGetValue().toString());

        String position = calculatePosition();
        ((TranslationContext) data).getAxisNodePositionsMap().put(node, position);

        getOutput(data).append(TranslationUtils.columnExpression(property.getLevel().getTableName(), property.getColumnName()));
        getOutput(data).append(" as ").append(position);
        getOutput(data).append(", ");

        super.visit(node, data);
        axisNodeVisitationStack.pop();
    }

    /**
     * Calcula a posição do nó na árvore abstrata baseada
     * na ordem de visitação do nó.
     * @return
     */
    private String calculatePosition() {
        StringBuilder position = new StringBuilder();
        for (int i = 0; i < axisNodeVisitationStack.size(); i++)
            if (i == 0)
                if (axisNodeVisitationStack.get(i) == 0)
                    position.append("r_");
                else
                    position.append("c_");
            else
                position.append(axisNodeVisitationStack.get(i)).append("_");
        position.deleteCharAt(position.length() - 1);

        return position.toString();
    }

    @Override
    public void visit(ASTAxis node, Object data) throws Exception {
        if (node.jjtGetValue().toString().equals("ROWS"))
            axisNodeVisitationStack.push(0);
        else
            axisNodeVisitationStack.push(1);

        super.visit(node, data);

        axisNodeVisitationStack.pop();
    }

    @Override
    public void visit(ASTLevelOrMeasureOrFilter node, Object data) throws Exception {
        axisNodeVisitationStack.push(childIndex(node));

        Metadata metadata = getQueryMetadata(data).getAllReferencedMetadata().getMeasure(node.jjtGetValue().toString());
        if (metadata != null) {
            MeasureSqlTranslator translator = new MeasureSqlTranslator(getQueryMetadata(data).getCube());
            getOutput(data).append(translator.translate(node.jjtGetValue().toString()));
        } else {
            metadata = getQueryMetadata(data).getAllReferencedMetadata().getLevel(node.jjtGetValue().toString());
            if (metadata != null)
                getOutput(data).append(TranslationUtils.columnExpression(((Level) metadata).getTableName(), ((Level) metadata).getCodeProperty().getColumnName()));
            else {
                metadata = getQueryMetadata(data).getAllReferencedMetadata().getFilter(node.jjtGetValue().toString());
                getOutput(data).append("case when ");
                translate(((Filter) metadata).getExpression(), (TranslationContext) data);
                getOutput(data).append(" then 1 else 0 end");
            }
        }

        String position = calculatePosition();
        ((TranslationContext) data).getAxisNodePositionsMap().put(node, position);
        getOutput(data).append(" as ").append(position).append(", ");

        super.visit(node, data);

        axisNodeVisitationStack.pop();
    }

    @Override
    public void visit(ASTInExpression node, Object data) throws Exception {
        getOutput(data).append(" in (");

        for (int i = 0; i < node.jjtGetNumChildren(); i++) {
            visit(node.jjtGetChild(i), data);
            getOutput(data).append(", ");
        }

        // delete last comma generated by axis translation
        getOutput(data).delete(getOutput(data).length() - 2, getOutput(data).length());
        getOutput(data).append(")");
    }

    @Override
    public void visit(ASTLikeExpression node, Object data) throws Exception {
        String operand = ((SimpleNode) node.jjtGetChild(0)).jjtGetValue().toString();

        StringBuilder sb = new StringBuilder();
        sb.append(TranslationUtils.likeWildcard());
        sb.append(TranslationUtils.discloseString(operand));
        sb.append(TranslationUtils.likeWildcard());

        getOutput(data).append(" like ");
        getOutput(data).append(TranslationUtils.encloseString(sb.toString()));
    }

    @Override
    public void visit(ASTStartsWithExpression node, Object data) throws Exception {
        String operand = ((SimpleNode) node.jjtGetChild(0)).jjtGetValue().toString();

        StringBuilder sb = new StringBuilder();
        sb.append(TranslationUtils.discloseString(operand));
        sb.append(TranslationUtils.likeWildcard());

        getOutput(data).append(" like ");
        getOutput(data).append(TranslationUtils.encloseString(sb.toString()));
    }

    @Override
    public void visit(ASTEndsWithExpression node, Object data) throws Exception {
        String operand = ((SimpleNode) node.jjtGetChild(0)).jjtGetValue().toString();

        StringBuilder sb = new StringBuilder();
        sb.append(TranslationUtils.likeWildcard());
        sb.append(TranslationUtils.discloseString(operand));

        getOutput(data).append(" like ");
        getOutput(data).append(TranslationUtils.encloseString(sb.toString()));
    }

    /**
     *
     * @param node
     * @param op
     * @param data
     * @throws Exception
     */
    private void visitOperation(SimpleNode node, String op, Object data) throws Exception {
        getOutput(data).append("(");

        for (int i = 0; i < node.jjtGetNumChildren(); i++) {
            visit(node.jjtGetChild(i), data);
            if (i < node.jjtGetNumChildren() - 1)
                getOutput(data).append(" ").append(op).append(" ");
        }

        getOutput(data).append(")");
    }

    /**
     * Retorna os níveis presentes na consulta, seja explicitamente em um nó, ou indiretamente
     * através de um nó filtro, ou filtro de métrica, ou ainda filtro na consulta.
     * @param query
     * @return
     */
    private List<Level> levelsPresent(TranslationContext context) {
        List<Level> levels = new ArrayList<Level>();

        for (Entry<String, Metadata> entry :
                context.getQueryMetadata().getAllReferencedMetadata().getInternalBag().entrySet())
            if (entry.getValue() instanceof Level)
                levels.add((Level) entry.getValue());
            else if (entry.getValue() instanceof Property)
                levels.add(((Property) entry.getValue()).getLevel());

        return levels;
    }

    /**
     * Produz a cláusula WHERE da consulta, baseado nos joins resultantes das referências
     * para os níveis da consulta.
     * @return
     */
    private String whereExpression(TranslationContext context) throws Exception {
        List<String> joins = new ArrayList<String>();

        List<Level> levels = levelsPresent(context);

        // do nível mais alto vai descendo e adicionando os joins até chegar o nível
        // mais baixo e assim juntá-lo ao cubo
        for (Level level : levels) {
            List<Level> lowerLevels = level.getLowerLevels();
            for (int i = 0; i < lowerLevels.size(); i++)
                // de duas em duas colunas, coloca o join na lista
                if (i > 0 & (i + 1) % 2 == 0) {
                    String upperColumn = TranslationUtils.columnExpression(lowerLevels.get(i - 1).getTableName(), lowerLevels.get(i - 1).getCodeProperty().getColumnName());
                    String thisColumn = TranslationUtils.columnExpression(lowerLevels.get(i).getTableName(), lowerLevels.get(i).getUpperLevelJoinColumn());
                    joins.add(thisColumn + " = " + upperColumn);
                }

            // o nível mais baixo (cujo índice é o maior) é o nível que faz junção com o cubo,
            // e indiretamente liga todos os níveis superiores também.
            Collections.sort(lowerLevels, new Comparator<Level>() {

                @Override
                public int compare(Level level1, Level level2) {
                    return Integer.valueOf(level1.getIndice()).compareTo(Integer.valueOf(level2.getIndice()));
                }
            });

            Level lowestLevel = lowerLevels.get(lowerLevels.size() - 1);

            for (CubeLevel cubeLevel : context.getQueryMetadata().getCube().getCubeLevelList())
                if (cubeLevel.getLevel().getId() == lowestLevel.getId())
                    joins.add(TranslationUtils.columnExpression(
                            context.getQueryMetadata().getCube().getTableName(),
                            cubeLevel.getJoinColumn()) + " = "
                            + TranslationUtils.columnExpression(
                            lowestLevel.getTableName(),
                            lowestLevel.getCodeProperty().getColumnName()));
        }

        if (!joins.isEmpty())
            for (int i = 0; i < joins.size(); i++) {
                context.getOutput().append(joins.get(i));
                if (i < joins.size() - 1)
                    context.getOutput().append(" and ");
            }

        return context.getOutput().toString();
    }

    /**
     * 
     * @param data
     * @return
     */
    private StringBuilder getOutput(Object data) {
        return ((TranslationContext) data).getOutput();
    }

    /**
     *
     * @param data
     * @return
     */
    private QueryMetadata getQueryMetadata(Object data) {
        return ((TranslationContext) data).getQueryMetadata();
    }

    /**
     * 
     * @param data
     * @return
     */
    private TranslationContext getContext(Object data) {
        return (TranslationContext) data;
    }

    /**
     * 
     * @param filterExpression
     * @param context
     * @throws Exception
     */
    private void translate(String filterExpression, TranslationContext context) throws Exception {
        QueryParser parser = new QueryParser(IOUtils.toInputStream(filterExpression));
        visit(parser.detachedFilterExpression(), context);
    }

    /**
     *
     * @param select
     * @return
     * @throws Exception
     */
    public TranslationContext translate(ASTSelect select) throws Exception {
        QueryMetadata queryMetadata = new QueryMetadata(select);
        TranslationContext context = new TranslationContext(queryMetadata);
        visit(select, context);
        return context;
    }

    /**
     *
     * @param filterExpression
     * @return
     * @throws Exception
     */
    public TranslationContext translate(ASTFilterExpression filterExpression) throws Exception {
        QueryMetadata queryMetadata = new QueryMetadata(filterExpression);
        TranslationContext context = new TranslationContext(queryMetadata);
        visit(filterExpression, context);
        return context;
    }

    @Override
    public void visit(ASTProperty node, Object data) throws Exception {
        Property property = getQueryMetadata(data).getAllReferencedMetadata().getProperty(node.jjtGetValue().toString());
        getOutput(data).append(TranslationUtils.columnExpression(property.getLevel().getTableName(), property.getColumnName()));
    }

    @Override
    public void visit(ASTCompare node, Object data) throws Exception {
        getOutput(data).append(" ").append(node.jjtGetValue()).append(" ");
    }

    @Override
    public void visit(ASTDateLiteral node, Object data) throws Exception {
        getOutput(data).append(node.jjtGetValue());
    }

    @Override
    public void visit(ASTStringLiteral node, Object data) throws Exception {
        String string = node.jjtGetValue().toString();
        getOutput(data).append(TranslationUtils.encloseString(TranslationUtils.discloseString(string)));
    }

    @Override
    public void visit(ASTAdditiveExpression node, Object data) throws Exception {
        visitOperation(node, data);
    }

    @Override
    public void visit(ASTMultiplicativeExpression node, Object data) throws Exception {
        visitOperation(node, data);
    }

    @Override
    public void visit(ASTNumberLiteral node, Object data) throws Exception {
        getOutput(data).append(node.jjtGetValue());
    }

    private void visitOperation(SimpleNode node, Object data) throws Exception {
        visitOperation(node, node.jjtGetValue().toString(), data);
    }

    @Override
    public void visit(ASTFilterExpression node, Object data) throws Exception {
        getOutput(data).append(" and ");
        visitChildren(node, data);
    }

    @Override
    public void visit(ASTOrCondition node, Object data) throws Exception {
        visitOperation(node, "or", data);
    }

    @Override
    public void visit(ASTAndCondition node, Object data) throws Exception {
        visitOperation(node, "and", data);
    }
}
